// -----------------------------------------------------------------------
// <copyright file="ContactPersonDB.cs" company="Imar.Spaanjaars.Com">
//   Copyright 2008 - 2009 - Imar.Spaanjaars.Com. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

using System;
using System.Data;
using System.Data.SqlClient;

using Spaanjaars.ContactManager.BusinessEntities;
using System.Data.Common;
using Spaanjaars.Validation;

namespace Spaanjaars.ContactManager.Dal
{
	/// <summary>
	/// The ContactPersonDB class is responsible for interacting with the database to retrieve and store information
	/// about ContactPerson objects.
	/// </summary>
	public class ContactPersonDB
	{
		#region Public Methods

		/// <summary>Gets an instance of ContactPerson from the underlying datasource.</summary>
		/// <param name="id">The unique ID of the ContactPerson in the database.</param>
		/// <returns>An ContactPerson when the ID was found in the database, or null otherwise.</returns>
		public static ContactPerson GetItem(int id)
		{
			ContactPerson myContactPerson = null;
			using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
			{
				using (SqlCommand myCommand = new SqlCommand("sprocContactPersonSelectSingleItem", myConnection))
				{
					myCommand.CommandType = CommandType.StoredProcedure;
					myCommand.Parameters.AddWithValue("@id", id);

					myConnection.Open();
					using (SqlDataReader myReader = myCommand.ExecuteReader())
					{
						if (myReader.Read())
						{
							myContactPerson = FillDataRecord(myReader);
						}
						myReader.Close();
					}
				}
				myConnection.Close();
			}
			return myContactPerson;
		}

		/// <summary>Returns a list with ContactPerson objects.</summary>
		/// <returns>A ContactPersonCollection with the ContactPerson objects.</returns>
		public static ContactPersonCollection GetList()
		{
			ContactPersonCollection tempList = new ContactPersonCollection();
			using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
			{
				using (SqlCommand myCommand = new SqlCommand("sprocContactPersonSelectList", myConnection))
				{
					myCommand.CommandType = CommandType.StoredProcedure;
					myConnection.Open();
					using (SqlDataReader myReader = myCommand.ExecuteReader())
					{
						if (myReader.HasRows)
						{
							tempList = new ContactPersonCollection();
							while (myReader.Read())
							{
								tempList.Add(FillDataRecord(myReader));
							}
						}
						myReader.Close();
					}
				}
			}
			return tempList;
		}

		/// <summary>
		/// This method lets you search for specific contact people based on the search criteria passed in.
		/// </summary>
		/// <param name="contactPersonCriteria">The criteria to search for.</param>
		public static ContactPersonCollection GetList(ContactPersonCriteria contactPersonCriteria)
		{
			ContactPersonCollection tempList = new ContactPersonCollection();
			using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
			{
				using (SqlCommand myCommand = new SqlCommand("sprocContactPersonSearchList", myConnection))
				{
					myCommand.CommandType = CommandType.StoredProcedure;

					if (!string.IsNullOrEmpty(contactPersonCriteria.FirstName))
					{
						myCommand.Parameters.AddWithValue("@firstName", contactPersonCriteria.FirstName);
					}
					if (!string.IsNullOrEmpty(contactPersonCriteria.LastName))
					{
						myCommand.Parameters.AddWithValue("@lastName", contactPersonCriteria.LastName);
					}
					if (contactPersonCriteria.PersonType != PersonType.None)
					{
						myCommand.Parameters.AddWithValue("@personType", contactPersonCriteria.PersonType);
					}

					myConnection.Open();
					using (SqlDataReader myReader = myCommand.ExecuteReader())
					{
						if (myReader.HasRows)
						{
							tempList = new ContactPersonCollection();
							while (myReader.Read())
							{
								tempList.Add(FillDataRecord(myReader));
							}
						}
						myReader.Close();
					}
				}
			}
			return tempList;
		}

		/// <summary>
		/// Returns the number of contact people in the database.
		/// </summary>
		public static int SelectCountForGetList()
		{
			using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
			{
				using (SqlCommand myCommand = new SqlCommand("sprocContactPersonSelectList", myConnection))
				{
					myCommand.CommandType = CommandType.StoredProcedure;
					DbParameter idParam = myCommand.CreateParameter();
					idParam.DbType = DbType.Int32;
					idParam.Direction = ParameterDirection.InputOutput;
					idParam.ParameterName = "@recordCount";
					idParam.Value = 0;
					myCommand.Parameters.Add(idParam);

					myConnection.Open();
					myCommand.ExecuteNonQuery();
					myConnection.Close();
					return (int)myCommand.Parameters["@recordCount"].Value;
				}
			}
		}



		/// <summary>Saves a contact person in the database.</summary>
		/// <param name="myContactPerson">The ContactPerson instance to save.</param>
		/// <returns>The new ID if the ContactPerson is new in the database or the existing ID when an item was updated.</returns>
		public static int Save(ContactPerson myContactPerson)
		{
			if (!myContactPerson.Validate())
			{
				throw new InvalidSaveOperationException("Can't save a ContactPerson in an Invalid state. Make sure that IsValid() returns true before you call Save().");
			}

			int result = 0;
			using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
			{
				using (SqlCommand myCommand = new SqlCommand("sprocContactPersonInsertUpdateSingleItem", myConnection))
				{
					myCommand.CommandType = CommandType.StoredProcedure;

					myCommand.Parameters.AddWithValue("@firstName", myContactPerson.FirstName);
					myCommand.Parameters.AddWithValue("@lastName", myContactPerson.LastName);
					if (string.IsNullOrEmpty(myContactPerson.MiddleName))
					{
						myCommand.Parameters.AddWithValue("@middleName", DBNull.Value);
					}
					else
					{
						myCommand.Parameters.AddWithValue("@middleName", myContactPerson.MiddleName);
					}
					myCommand.Parameters.AddWithValue("@dateOfBirth", myContactPerson.DateOfBirth);
					myCommand.Parameters.AddWithValue("@contactpersonType", myContactPerson.Type);

					Helpers.SetSaveParameters(myCommand, myContactPerson);

					myConnection.Open();
					int numberOfRecordsAffected = myCommand.ExecuteNonQuery();
					if (numberOfRecordsAffected == 0)
					{
						throw new DBConcurrencyException("Can't update contact person as it has been updated by someone else");
					}

					myContactPerson.ConcurrencyId = Helpers.GetConcurrencyId(myCommand); ;
					result = Helpers.GetBusinessBaseId(myCommand);
				}
				myConnection.Close();
			}
			return result;
		}

		/// <summary>Deletes a contact person from the database.</summary>
		/// <param name="id">The ID of the contact person to delete.</param>
		/// <returns>Returns <c>true</c> when the object was deleted successfully, or <c>false</c> otherwise.</returns>
		public static bool Delete(int id)
		{
			int result = 0;
			using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
			{
				using (SqlCommand myCommand = new SqlCommand("sprocContactPersonDeleteSingleItem", myConnection))
				{
					myCommand.CommandType = CommandType.StoredProcedure;
					myCommand.Parameters.AddWithValue("@id", id);
					myConnection.Open();
					result = myCommand.ExecuteNonQuery();
				}
				myConnection.Close();
			}
			return result > 0;
		}

		#endregion

		#region Private Methods

		/// <summary>
		/// Initializes a new instance of the ContactPerson class and fills it with the data fom the IDataRecord.
		/// </summary>
		private static ContactPerson FillDataRecord(IDataRecord myDataRecord)
		{
			ContactPerson myContactPerson = new ContactPerson();

			myContactPerson.Id = myDataRecord.GetInt32(myDataRecord.GetOrdinal("Id"));
			myContactPerson.FirstName = myDataRecord.GetString(myDataRecord.GetOrdinal("FirstName"));
			if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("MiddleName")))
			{
				myContactPerson.MiddleName = myDataRecord.GetString(myDataRecord.GetOrdinal("MiddleName"));
			}
			myContactPerson.LastName = myDataRecord.GetString(myDataRecord.GetOrdinal("LastName"));
			if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DateOfBirth")))
			{
				myContactPerson.DateOfBirth = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("DateOfBirth"));
			}
			myContactPerson.Type = (PersonType)myDataRecord.GetInt32(myDataRecord.GetOrdinal("ContactpersonType"));
			myContactPerson.ConcurrencyId = (byte[])myDataRecord.GetValue(myDataRecord.GetOrdinal("ConcurrencyId"));

			return myContactPerson;
		}
		#endregion
	}
}